import xlrd

def read_execl(file_path,sheet_index):
    ip_list = []
    count=0
    workbook =xlrd.open_workbook(file_path) #打开excel工作簿
    #print(workbook.sheet_names())    #打印sheet name
    sheet =workbook.sheet_by_index(sheet_index)    #选择第n张sheet,从0开始
    #sheet =workbook.sheet_by_name(sheet_name)  #按名字取sheet
    for row in range(sheet.nrows):      #第一个for循环遍历所有行
        if  sheet.row(row)[0].value == 'beijing':
            ip_list.append(sheet.row(row)[1].value.strip())
            count += 1
            #print(sheet.row(row)[0].value)
            #print(sheet.row(row)[1].value)

        '''
        for col in range(sheet.ncols):    #第二个for循环遍历所有列，这样就找到某一个xy对应的元素，就可以打印出来
            print("%7s"%sheet.row(row)[col].value,'\t',end='')
       '''
    print(count)
    return ip_list

def compare_ip():
    file_path1 ='比对结果与清单.xlsx'
    ip_list1 = read_execl(file_path1,1)
    ip_list2 = read_execl(file_path1,2)
    
    file_path2 ='主机.xlsx'
    ip_list3 = read_execl(file_path2,0)
    
    found_count = 0
    for ip in ip_list1:
        #print("%s" % ip, end = ' ' )
        found = 0
        for i in range(len(ip_list3)):
             if ip == ip_list3[i]:
                found = 1
                break
        if found == 0:
            print(ip, end = ' ')
            found_count += 1
    print("\r\n%d"%found_count)

def find_dup_ip():
    file_path ='主机.xlsx'
    ip_list = read_execl(file_path,0)
    ip_list_len=len(ip_list)
    dup_index_list = []

    print("====find repeat ip====")
    for i,ip in enumerate(ip_list):
        if i in (dup_index_list):
            continue
        for j in range(i+1,ip_list_len):
            if ip == ip_list[j]:
                dup_index_list.append(j)
                print("%d  %d  %s|" %(i,j,ip),end='')
    dup_index_list.sort()
    print("\r\nFound ips:%d"%ip_list_len)
    print("found dup ips: %d"%(len(dup_index_list)))
    print(dup_index_list)

if __name__ == '__main__':
    #compare_ip()
    find_dup_ip()
            

        